C#應用(4)jqGrid的條件式搜尋

DAY 4

c#應用系列第 4 篇

c# 鐵人賽 c#

player

2013-09-19 12:05:29

5574 瀏覽

分享至

jqGrid是一套基於jQuery基礎的JavaScript的Grid套件
常被用在寫SPA(單頁應用程式)

這篇主要是寫ASP.NET MVC在使用jqGrid時
Server端用的搜尋相關程式碼

回目錄
原始版本的出處:Using jqGrid’s search toolbar with multiple filters in ASP.NET MVC

以下為搜尋功能的相關修正 (沒改的檔案, 請照原來的版本照用)
以下修正過的內容曾於2013年06月13日OpenSource
並於2013年07月30日再次修正相關bug
不保證沒有遺漏掉的Bug, 如果有人在使用上有任何問題, 日後再另行修正

*檔案 Filter.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Json;
 
namespace MvcGrid.Models.Grid
{
    [DataContract]
    public class Filter
    {
        [DataMember]
        public string groupOp { get; set; }
        [DataMember]
        public Rule[] rules { get; set; }
 
        public static Filter Create(string jsonData)
        {
            try
            {
                var serializer = new DataContractJsonSerializer(typeof(Filter));
                System.IO.StringReader reader = new System.IO.StringReader(jsonData);
                System.IO.MemoryStream ms =
                    new System.IO.MemoryStream(Encoding.Unicode.GetBytes(jsonData));
 
                return serializer.ReadObject(ms) as Filter;
            }
            catch
            {
                return null;
            }
        }
    }
}

*檔案 WhereOperation.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Json;
 
namespace MvcGrid.Models.Grid
{
    [DataContract]
    public class Filter
    {
        [DataMember]
        public string groupOp { get; set; }
        [DataMember]
        public Rule[] rules { get; set; }
 
        public static Filter Create(string jsonData)
        {
            try
            {
                var serializer = new DataContractJsonSerializer(typeof(Filter));
                System.IO.StringReader reader = new System.IO.StringReader(jsonData);
                System.IO.MemoryStream ms =
                    new System.IO.MemoryStream(Encoding.Unicode.GetBytes(jsonData));
 
                return serializer.ReadObject(ms) as Filter;
            }
            catch
            {
                return null;
            }
        }
    }
}

*檔案 WhereOperation.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
 
namespace MvcGrid.Models.Helpers
{
    /// <summary>
    /// The supported operations in where-extension
    /// </summary>
    public enum WhereOperation
    {
        //等於
        [StringValue("eq")]
        Equal,
 
        //不等於
        [StringValue("ne")]
        NotEqual,
 
        //小於
        [StringValue("lt")]
        LessThan,
 
        //小於等於
        [StringValue("le")]
        LessThanOrEqual,
 
        //大於
        [StringValue("gt")]
        GreaterThan,
 
        //大於等於
        [StringValue("ge")]
        GreaterThanOrEqual,
 
        //開始於
        [StringValue("bw")]
        BeginsWith,
 
        //不開始於
        [StringValue("bn")]
        NotBeginsWith,
 
        //在其中
        [StringValue("in")]
        In,
 
        //不在其中
        [StringValue("ni")]
        NotIn,
 
        //結束於="ew"
        [StringValue("ew")]
        EndWith,
 
        //不結束於
        [StringValue("en")]
        NotEndWith,
 
        //包含
        [StringValue("cn")]
        Contains,
 
        //不包含
        [StringValue("nc")]
        NotContains,
 
        //Null
        [StringValue("nu")]
        Null,
 
        //不Null
        [StringValue("nn")]
        NotNull
    }
}

*檔案 LinqExtensions.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
 
namespace MvcGrid.Models.Helpers
{
 
    public static class LinqExtensions
    {
        /// <summary>Orders the sequence by specific column and direction.</summary>
        /// <param name="query">The query.</param>
        /// <param name="sortColumn">The sort column.</param>
        /// <param name="ascending">if set to true [ascending].</param>
        public static IQueryable<T> OrderBy<T>(this IQueryable<T> query, string sortColumn, string direction)
        {
            string methodName = string.Format("OrderBy{0}",
                direction.ToLower() == "asc" ? "" : "Descending");
 
            ParameterExpression parameter = Expression.Parameter(query.ElementType, "p");
 
            MemberExpression memberAccess = null;
            foreach (var property in sortColumn.Split('.'))
                memberAccess = MemberExpression.Property
                   (memberAccess ?? (parameter as Expression), property);
 
            LambdaExpression orderByLambda = Expression.Lambda(memberAccess, parameter);
 
            MethodCallExpression result = Expression.Call(
                      typeof(Queryable),
                      methodName,
                      new[] { query.ElementType, memberAccess.Type },
                      query.Expression,
                      Expression.Quote(orderByLambda));
 
            return query.Provider.CreateQuery<T>(result);
        }
 
 
        public static IQueryable<T> Where<T>(this IQueryable<T> query,
            string column, object value, WhereOperation operation)
        {
            if (string.IsNullOrEmpty(column))
                return query;
 
            ParameterExpression parameter = Expression.Parameter(query.ElementType, "p");
 
            MemberExpression memberAccess = null;
            foreach (var property in column.Split('.'))
                memberAccess = MemberExpression.Property
                   (memberAccess ?? (parameter as Expression), property);
 
            //change param value type
            //necessary to getting bool from string
            ConstantExpression filter = Expression.Constant
                (
                    Convert.ChangeType(value, memberAccess.Type)
                );
 
            //switch operation
            Expression condition = null;
            LambdaExpression lambda = null;
            switch (operation)
            {
                //等於 equal ==
                case WhereOperation.Equal:
                    condition = Expression.Equal(memberAccess, filter);
                    lambda = Expression.Lambda(condition, parameter);
                    break;
 
                //不等於 not equal !=
                case WhereOperation.NotEqual:
                    condition = Expression.NotEqual(memberAccess, filter);
                    lambda = Expression.Lambda(condition, parameter);
                    break;
 
                //小於
                case WhereOperation.LessThan:
                    condition = Expression.LessThan(memberAccess, filter);
                    lambda = Expression.Lambda(condition, parameter);
                    break;
 
                //小於等於
                case WhereOperation.LessThanOrEqual:
                    condition = Expression.LessThanOrEqual(memberAccess, filter);
                    lambda = Expression.Lambda(condition, parameter);
                    break;
 
                //大於
                case WhereOperation.GreaterThan:
                    condition = Expression.GreaterThan(memberAccess, filter);
                    lambda = Expression.Lambda(condition, parameter);
                    break;
 
                //大於等於
                case WhereOperation.GreaterThanOrEqual:
                    condition = Expression.GreaterThanOrEqual(memberAccess, filter);
                    lambda = Expression.Lambda(condition, parameter);
                    break;
 
                //開始於
                case WhereOperation.BeginsWith:
                    condition = Expression.Call(memberAccess,
                        typeof(string).GetMethod("StartsWith", new[] { typeof(string) }),
                        Expression.Constant(value));
                    lambda = Expression.Lambda(condition, parameter);
                    break;
 
                //不開始於
                case WhereOperation.NotBeginsWith:
                    condition = Expression.Call(memberAccess,
                        typeof(string).GetMethod("StartsWith", new[] { typeof(string) }), 
                        Expression.Constant(value));
                    condition = Expression.Not(condition);
                    lambda = Expression.Lambda(condition, parameter);
                    break;
 
                //在其中 string.Contains()
                case WhereOperation.In:
                    condition = Expression.Call(memberAccess, 
                        typeof(string).GetMethod("Contains", new[] { typeof(string) }), 
                        Expression.Constant(value));
                    lambda = Expression.Lambda(condition, parameter);
                    break
                        ;
                //不在其中 string.Contains()
                case WhereOperation.NotIn:
                    condition = Expression.Call(memberAccess,
                        typeof(string).GetMethod("Contains", new[] { typeof(string) }), 
                        Expression.Constant(value));
                    condition = Expression.Not(condition);
                    lambda = Expression.Lambda(condition, parameter);
                    break;
 
                //結束於
                case WhereOperation.EndWith:
                    condition = Expression.Call(memberAccess,
                        typeof(string).GetMethod("EndsWith", new[] { typeof(string) }),
                        Expression.Constant(value));
                    lambda = Expression.Lambda(condition, parameter);
                    break;
 
                //不結束於
                case WhereOperation.NotEndWith:
                    condition = Expression.Call(memberAccess,
                        typeof(string).GetMethod("EndsWith", new[] { typeof(string) }),
                        Expression.Constant(value));
                    condition = Expression.Not(condition);
                    lambda = Expression.Lambda(condition, parameter);
                    break;
 
                //包含 string.Contains()
                case WhereOperation.Contains:
                    condition = Expression.Call(memberAccess,
                        typeof(string).GetMethod("Contains", new[] { typeof(string) }),
                        Expression.Constant(value));
                    lambda = Expression.Lambda(condition, parameter);
                    break;
 
                //不包含
                case WhereOperation.NotContains:
                    condition = Expression.Call(memberAccess,
                        typeof(string).GetMethod("Contains", new[] { typeof(string) }),
                        Expression.Constant(value));
                    condition = Expression.Not(condition);
                    lambda = Expression.Lambda(condition, parameter);
                    break;
 
                //Null
                case WhereOperation.Null:
                    condition = Expression.Call(memberAccess, 
                        typeof(string).GetMethod("IsNullOrEmpty"), 
                        Expression.Constant(value));
                    lambda = Expression.Lambda(condition, parameter);
                    break;
 
                //不Null
                case WhereOperation.NotNull:
                    condition = Expression.Call(memberAccess, 
                        typeof(string).GetMethod("IsNullOrEmpty"), 
                        Expression.Constant(value));
                    condition = Expression.Not(condition);
                    lambda = Expression.Lambda(condition, parameter);
                    break;
            }
 
 
            MethodCallExpression result = Expression.Call(
                   typeof(Queryable), "Where",
                   new[] { query.ElementType },
                   query.Expression,
                   lambda);
 
            return query.Provider.CreateQuery<T>(result);
        }
    }
}